--DO NOT MODIFY THIS FILE. IT IS ALWAYS OVERWRITTEN ON GENERATION.
--Data Schema For Version 0.0.0.0
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_EmployeeDelete]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_EmployeeDelete]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[gen_EmployeeDelete]
(
	@Original_EmployeeID Int
)
AS
SET NOCOUNT OFF;

DELETE FROM 
	[Employees] 
WHERE 
	[EmployeeID] = @Original_EmployeeID ;

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_EmployeeSelectByEmployeePks]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_EmployeeSelectByEmployeePks]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE PROCEDURE [dbo].[gen_EmployeeSelectByEmployeePks]
(
	@xml ntext,
	@direction char(4),
	@levels int
)
AS

DECLARE @hDoc int
DECLARE @newItemCount int
DECLARE @count int
DECLARE @index int
EXEC sp_xml_preparedocument @hDoc OUTPUT, @xml

CREATE TABLE #TmpIds ([EmployeeID] Int)


INSERT INTO #TmpIds([EmployeeID])
SELECT
	[EmployeeID]
FROM 
	OpenXML(@hDoc, '//Employees', 2) 
WITH (
	[EmployeeID] Int 'EmployeeID'
	)

IF (@direction = 'DOWN' OR @direction = 'BOTH')
BEGIN
	SET @newItemCount = (SELECT COUNT(*) FROM #TmpIds)
	SET @count = @newItemCount
	SET @index = 1
	WHILE(@newItemCount > 0 and @index <= @levels)
	BEGIN
		INSERT INTO #TmpIds([EmployeeID])
		SELECT
			[Employee].[ReportsTo]

		FROM 
			[Employee]
			INNER JOIN #TmpIds ON 
[Employees].[ReportsTo] = #TmpIds.[EmployeeID]
		WHERE
			[Employee].[ReportsTo] NOT IN (SELECT [EmployeeID] from #TmpIds)


		SET @newItemCount = (SELECT COUNT(*) FROM #TmpIds) - @count
		SET @count = (SELECT COUNT(*) FROM #TmpIds)
		SET @index = @index + 1
	END
END
IF (@direction = 'UP' OR @direction = 'BOTH')
BEGIN
	SET @newItemCount = (SELECT COUNT(*) FROM #TmpIds)
	SET @count = @newItemCount
	SET @index = 1
	WHILE(@newItemCount > 0 and @index <= @levels)
	BEGIN
		INSERT INTO #TmpIds([ReportsTo])
		SELECT
	[Employees].[ReportsTo]
		FROM #TmpIds INNER JOIN [Employees] ON [Employees].[ReportsTo] = #TmpIds.[EmployeeID]
		WHERE
			[Employees].[ReportsTo] NOT IN (SELECT [EmployeeID] from #TmpIds)

		SET @newItemCount = (SELECT COUNT(*) FROM #TmpIds) - @count
		SET @count = (SELECT COUNT(*) FROM #TmpIds)
		SET @index = @index + 1
	END
END

SELECT
		[Employees].[titleofcourtesy],
	[Employees].[title],
	[Employees].[reportsto],
	[Employees].[region],
	[Employees].[postalcode],
	[Employees].[photopath],
	[Employees].[photo],
	[Employees].[notes],
	[Employees].[lastname],
	[Employees].[homephone],
	[Employees].[hiredate],
	[Employees].[firstname],
	[Employees].[extension],
	[Employees].[employeeid],
	[Employees].[country],
	[Employees].[city],
	[Employees].[birthdate],
	[Employees].[address],
	[Employees].[created_by],
	[Employees].[created_date],
	[Employees].[modified_by],
	[Employees].[modified_date],
	[Employees].[time_stamp]

FROM 
[Employees]
WHERE
			([Employees].[ReportsTo] IN (SELECT [EmployeeID] from #TmpIds))


DROP TABLE #TmpIds
exec sp_xml_removeDocument @hDoc


GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_EmployeeSelectByEmployeeSinglePk]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_EmployeeSelectByEmployeeSinglePk]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[gen_EmployeeSelectByEmployeeSinglePk]
(
	@EmployeeID Int
)
AS

SELECT 
	[Employees].[titleofcourtesy],
	[Employees].[title],
	[Employees].[reportsto],
	[Employees].[region],
	[Employees].[postalcode],
	[Employees].[photopath],
	[Employees].[photo],
	[Employees].[notes],
	[Employees].[lastname],
	[Employees].[homephone],
	[Employees].[hiredate],
	[Employees].[firstname],
	[Employees].[extension],
	[Employees].[employeeid],
	[Employees].[country],
	[Employees].[city],
	[Employees].[birthdate],
	[Employees].[address],
	[Employees].[created_by],
	[Employees].[created_date],
	[Employees].[modified_by],
	[Employees].[modified_date],
	[Employees].[time_stamp]

FROM
[Employees]
WHERE 
[Employees].[EmployeeID] = @EmployeeID 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_EmployeeInsert]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_EmployeeInsert]
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE PROCEDURE [dbo].[gen_EmployeeInsert]
(
	@TitleOfCourtesy NVarChar(25) = default,
	@Title NVarChar(30) = default,
	@ReportsTo Int = default,
	@Region NVarChar(15) = default,
	@PostalCode NVarChar(10) = default,
	@PhotoPath NVarChar(255) = default,
	@Photo Image = default,
	@Notes NText = default,
	@LastName NVarChar(20) = default,
	@HomePhone NVarChar(24) = default,
	@HireDate DateTime = default,
	@FirstName NVarChar(10) = default,
	@Extension NVarChar(4) = default,
	@EmployeeID Int = default,
	@Country NVarChar(15) = default,
	@City NVarChar(15) = default,
	@BirthDate DateTime = default,
	@Address NVarChar(60) = default,
	@created_date datetime,
	@created_by varchar (50),
	@modified_by varchar (50)

)
AS
SET NOCOUNT OFF;

if (@created_date IS NULL)
SET @created_date = GetDate()
IF (@EmployeeID < 0) SET @EmployeeID = NULL;
if ((@EmployeeID IS NULL))
BEGIN

INSERT INTO [Employees]
(
[Address],
[BirthDate],
[City],
[Country],
[Extension],
[FirstName],
[HireDate],
[HomePhone],
[LastName],
[Notes],
[Photo],
[PhotoPath],
[PostalCode],
[Region],
[ReportsTo],
[Title],
[TitleOfCourtesy],
[created_date],
[created_by],
[modified_date],
[modified_by]

)
VALUES
(
	@Address,
	@BirthDate,
	@City,
	@Country,
	@Extension,
	@FirstName,
	@HireDate,
	@HomePhone,
	@LastName,
	@Notes,
	@Photo,
	@PhotoPath,
	@PostalCode,
	@Region,
	@ReportsTo,
	@Title,
	@TitleOfCourtesy,
	@created_date,
	@created_by,
	@created_date,
	@modified_by

);

END
ELSE
BEGIN
SET identity_insert [Employees] on
INSERT INTO [Employees]
(
[EmployeeID],
[Address],
[BirthDate],
[City],
[Country],
[Extension],
[FirstName],
[HireDate],
[HomePhone],
[LastName],
[Notes],
[Photo],
[PhotoPath],
[PostalCode],
[Region],
[ReportsTo],
[Title],
[TitleOfCourtesy],
[created_date],
[created_by],
[modified_date],
[modified_by]

)
VALUES
(
	@EmployeeID,
	@Address,
	@BirthDate,
	@City,
	@Country,
	@Extension,
	@FirstName,
	@HireDate,
	@HomePhone,
	@LastName,
	@Notes,
	@Photo,
	@PhotoPath,
	@PostalCode,
	@Region,
	@ReportsTo,
	@Title,
	@TitleOfCourtesy,
	@created_date,
	@created_by,
	@created_date,
	@modified_by

);
SET identity_insert [Employees] off
END


SELECT 
	[Employees].[titleofcourtesy],
	[Employees].[title],
	[Employees].[reportsto],
	[Employees].[region],
	[Employees].[postalcode],
	[Employees].[photopath],
	[Employees].[photo],
	[Employees].[notes],
	[Employees].[lastname],
	[Employees].[homephone],
	[Employees].[hiredate],
	[Employees].[firstname],
	[Employees].[extension],
	[Employees].[employeeid],
	[Employees].[country],
	[Employees].[city],
	[Employees].[birthdate],
	[Employees].[address],
	[Employees].[created_by],
	[Employees].[created_date],
	[Employees].[modified_by],
	[Employees].[modified_date],
	[Employees].[time_stamp]

FROM
[Employees]
WHERE
	[Employees].[EmployeeID] = SCOPE_IDENTITY();
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_EmployeePagingSelect]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_EmployeePagingSelect]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[gen_EmployeePagingSelect]
(
	@page int, -- page number selected by the user
	@pageSize int, -- number of items on the page
	@orderByColumn varchar(100), -- name of column to order things by
	@ascending bit, -- order column ascending or descending
	@filter varchar(100) = null, -- filter statement passed in to determine like criteria on order by column
	@count int out -- number of items in the collection
)
AS

SET NOCOUNT ON;


CREATE TABLE #tmpTable
(
[EmployeeID] Int
)

DECLARE @total__ivqatedr int
DECLARE @orderByColumnIndex int
-- remove top x values from the temp table based upon the specific page requested
SET @total__ivqatedr = (@pageSize * @page)
IF (@total__ivqatedr <> 0)
BEGIN
	SET ROWCOUNT @total__ivqatedr
END
INSERT INTO #tmpTable
(
	[employeeid]
)
SELECT
	[Employees].[employeeid]
FROM
[Employees]
WHERE
	(@orderByColumn = 'TitleOfCourtesy' and (((@filter is null) or ([Employees].[TitleOfCourtesy] is null)) or (@filter is not null and [Employees].[TitleOfCourtesy] LIKE @filter)))
or
	(@orderByColumn = 'Title' and (((@filter is null) or ([Employees].[Title] is null)) or (@filter is not null and [Employees].[Title] LIKE @filter)))
or
	(@orderByColumn = 'ReportsTo' and (((@filter is null) or ([Employees].[ReportsTo] is null)) or (@filter is not null and [Employees].[ReportsTo] = @filter)))
or
	(@orderByColumn = 'Region' and (((@filter is null) or ([Employees].[Region] is null)) or (@filter is not null and [Employees].[Region] LIKE @filter)))
or
	(@orderByColumn = 'PostalCode' and (((@filter is null) or ([Employees].[PostalCode] is null)) or (@filter is not null and [Employees].[PostalCode] LIKE @filter)))
or
	(@orderByColumn = 'PhotoPath' and (((@filter is null) or ([Employees].[PhotoPath] is null)) or (@filter is not null and [Employees].[PhotoPath] LIKE @filter)))
or
	(@orderByColumn = 'LastName' and (((@filter is null) or ([Employees].[LastName] is null)) or (@filter is not null and [Employees].[LastName] LIKE @filter)))
or
	(@orderByColumn = 'HomePhone' and (((@filter is null) or ([Employees].[HomePhone] is null)) or (@filter is not null and [Employees].[HomePhone] LIKE @filter)))
or
	(@orderByColumn = 'HireDate' and (((@filter is null) or ([Employees].[HireDate] is null)) or (@filter is not null and [Employees].[HireDate] = @filter)))
or
	(@orderByColumn = 'FirstName' and (((@filter is null) or ([Employees].[FirstName] is null)) or (@filter is not null and [Employees].[FirstName] LIKE @filter)))
or
	(@orderByColumn = 'Extension' and (((@filter is null) or ([Employees].[Extension] is null)) or (@filter is not null and [Employees].[Extension] LIKE @filter)))
or
	(@orderByColumn = 'EmployeeID' and (((@filter is null) or ([Employees].[EmployeeID] is null)) or (@filter is not null and [Employees].[EmployeeID] = @filter)))
or
	(@orderByColumn = 'Country' and (((@filter is null) or ([Employees].[Country] is null)) or (@filter is not null and [Employees].[Country] LIKE @filter)))
or
	(@orderByColumn = 'City' and (((@filter is null) or ([Employees].[City] is null)) or (@filter is not null and [Employees].[City] LIKE @filter)))
or
	(@orderByColumn = 'BirthDate' and (((@filter is null) or ([Employees].[BirthDate] is null)) or (@filter is not null and [Employees].[BirthDate] = @filter)))
or
	(@orderByColumn = 'Address' and (((@filter is null) or ([Employees].[Address] is null)) or (@filter is not null and [Employees].[Address] LIKE @filter)))
ORDER BY
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'TitleOfCourtesy' THEN [Employees].[TitleOfCourtesy] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'TitleOfCourtesy' THEN [Employees].[TitleOfCourtesy] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'Title' THEN [Employees].[Title] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'Title' THEN [Employees].[Title] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'ReportsTo' THEN [Employees].[ReportsTo] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'ReportsTo' THEN [Employees].[ReportsTo] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'Region' THEN [Employees].[Region] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'Region' THEN [Employees].[Region] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'PostalCode' THEN [Employees].[PostalCode] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'PostalCode' THEN [Employees].[PostalCode] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'PhotoPath' THEN [Employees].[PhotoPath] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'PhotoPath' THEN [Employees].[PhotoPath] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'LastName' THEN [Employees].[LastName] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'LastName' THEN [Employees].[LastName] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'HomePhone' THEN [Employees].[HomePhone] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'HomePhone' THEN [Employees].[HomePhone] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'HireDate' THEN [Employees].[HireDate] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'HireDate' THEN [Employees].[HireDate] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'FirstName' THEN [Employees].[FirstName] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'FirstName' THEN [Employees].[FirstName] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'Extension' THEN [Employees].[Extension] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'Extension' THEN [Employees].[Extension] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'EmployeeID' THEN [Employees].[EmployeeID] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'EmployeeID' THEN [Employees].[EmployeeID] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'Country' THEN [Employees].[Country] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'Country' THEN [Employees].[Country] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'City' THEN [Employees].[City] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'City' THEN [Employees].[City] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'BirthDate' THEN [Employees].[BirthDate] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'BirthDate' THEN [Employees].[BirthDate] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'Address' THEN [Employees].[Address] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'Address' THEN [Employees].[Address] END END

-- set @count based on the rows moved in the previous statement
SET ROWCOUNT 0
SET @count = (
SELECT count(*)
FROM
[Employees]
WHERE
	(@orderByColumn = 'TitleOfCourtesy' and (((@filter is null) or ([Employees].[TitleOfCourtesy] is null)) or (@filter is not null and [Employees].[TitleOfCourtesy] LIKE @filter)))
or
	(@orderByColumn = 'Title' and (((@filter is null) or ([Employees].[Title] is null)) or (@filter is not null and [Employees].[Title] LIKE @filter)))
or
	(@orderByColumn = 'ReportsTo' and (((@filter is null) or ([Employees].[ReportsTo] is null)) or (@filter is not null and [Employees].[ReportsTo] = @filter)))
or
	(@orderByColumn = 'Region' and (((@filter is null) or ([Employees].[Region] is null)) or (@filter is not null and [Employees].[Region] LIKE @filter)))
or
	(@orderByColumn = 'PostalCode' and (((@filter is null) or ([Employees].[PostalCode] is null)) or (@filter is not null and [Employees].[PostalCode] LIKE @filter)))
or
	(@orderByColumn = 'PhotoPath' and (((@filter is null) or ([Employees].[PhotoPath] is null)) or (@filter is not null and [Employees].[PhotoPath] LIKE @filter)))
or
	(@orderByColumn = 'LastName' and (((@filter is null) or ([Employees].[LastName] is null)) or (@filter is not null and [Employees].[LastName] LIKE @filter)))
or
	(@orderByColumn = 'HomePhone' and (((@filter is null) or ([Employees].[HomePhone] is null)) or (@filter is not null and [Employees].[HomePhone] LIKE @filter)))
or
	(@orderByColumn = 'HireDate' and (((@filter is null) or ([Employees].[HireDate] is null)) or (@filter is not null and [Employees].[HireDate] = @filter)))
or
	(@orderByColumn = 'FirstName' and (((@filter is null) or ([Employees].[FirstName] is null)) or (@filter is not null and [Employees].[FirstName] LIKE @filter)))
or
	(@orderByColumn = 'Extension' and (((@filter is null) or ([Employees].[Extension] is null)) or (@filter is not null and [Employees].[Extension] LIKE @filter)))
or
	(@orderByColumn = 'EmployeeID' and (((@filter is null) or ([Employees].[EmployeeID] is null)) or (@filter is not null and [Employees].[EmployeeID] = @filter)))
or
	(@orderByColumn = 'Country' and (((@filter is null) or ([Employees].[Country] is null)) or (@filter is not null and [Employees].[Country] LIKE @filter)))
or
	(@orderByColumn = 'City' and (((@filter is null) or ([Employees].[City] is null)) or (@filter is not null and [Employees].[City] LIKE @filter)))
or
	(@orderByColumn = 'BirthDate' and (((@filter is null) or ([Employees].[BirthDate] is null)) or (@filter is not null and [Employees].[BirthDate] = @filter)))
or
	(@orderByColumn = 'Address' and (((@filter is null) or ([Employees].[Address] is null)) or (@filter is not null and [Employees].[Address] LIKE @filter)))
)

-- remove top x values from the temp table based upon the specific page requested
SET @total__ivqatedr = (@pageSize * @page) - @pageSize
IF (@total__ivqatedr <> 0)
BEGIN
	SET ROWCOUNT @total__ivqatedr
	DELETE FROM #tmpTable
END

-- return the number of rows requested as the page size
SET ROWCOUNT @pageSize
SELECT
	[Employees].[titleofcourtesy],
	[Employees].[title],
	[Employees].[reportsto],
	[Employees].[region],
	[Employees].[postalcode],
	[Employees].[photopath],
	[Employees].[photo],
	[Employees].[notes],
	[Employees].[lastname],
	[Employees].[homephone],
	[Employees].[hiredate],
	[Employees].[firstname],
	[Employees].[extension],
	[Employees].[employeeid],
	[Employees].[country],
	[Employees].[city],
	[Employees].[birthdate],
	[Employees].[address],
	[Employees].[created_by],
	[Employees].[created_date],
	[Employees].[modified_by],
	[Employees].[modified_date],
	[Employees].[time_stamp]
FROM
	[#tmpTable]
	INNER JOIN [Employees] ON #tmpTable.[employeeid] = [EMPLOYEES].[employeeid]
ORDER BY
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'TitleOfCourtesy' THEN [Employees].[TitleOfCourtesy] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'TitleOfCourtesy' THEN [Employees].[TitleOfCourtesy] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'Title' THEN [Employees].[Title] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'Title' THEN [Employees].[Title] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'ReportsTo' THEN [Employees].[ReportsTo] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'ReportsTo' THEN [Employees].[ReportsTo] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'Region' THEN [Employees].[Region] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'Region' THEN [Employees].[Region] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'PostalCode' THEN [Employees].[PostalCode] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'PostalCode' THEN [Employees].[PostalCode] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'PhotoPath' THEN [Employees].[PhotoPath] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'PhotoPath' THEN [Employees].[PhotoPath] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'LastName' THEN [Employees].[LastName] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'LastName' THEN [Employees].[LastName] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'HomePhone' THEN [Employees].[HomePhone] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'HomePhone' THEN [Employees].[HomePhone] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'HireDate' THEN [Employees].[HireDate] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'HireDate' THEN [Employees].[HireDate] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'FirstName' THEN [Employees].[FirstName] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'FirstName' THEN [Employees].[FirstName] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'Extension' THEN [Employees].[Extension] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'Extension' THEN [Employees].[Extension] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'EmployeeID' THEN [Employees].[EmployeeID] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'EmployeeID' THEN [Employees].[EmployeeID] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'Country' THEN [Employees].[Country] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'Country' THEN [Employees].[Country] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'City' THEN [Employees].[City] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'City' THEN [Employees].[City] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'BirthDate' THEN [Employees].[BirthDate] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'BirthDate' THEN [Employees].[BirthDate] END END, 
	CASE @ascending WHEN 0 THEN CASE @orderByColumn WHEN 'Address' THEN [Employees].[Address] END END DESC, 
	CASE @ascending WHEN 1 THEN CASE @orderByColumn WHEN 'Address' THEN [Employees].[Address] END END

DROP TABLE #tmpTable

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_EmployeeSelectBySearchAnd]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_EmployeeSelectBySearchAnd]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[gen_EmployeeSelectBySearchAnd]
(
	@TitleOfCourtesy NVarChar(25) = null, 
	@Title NVarChar(30) = null, 
	@ReportsTo Int = null, 
	@Region NVarChar(15) = null, 
	@PostalCode NVarChar(10) = null, 
	@PhotoPath NVarChar(255) = null, 
	@LastName NVarChar(20) = null, 
	@HomePhone NVarChar(24) = null, 
	@HireDate DateTime = null, 
	@FirstName NVarChar(10) = null, 
	@Extension NVarChar(4) = null, 
	@EmployeeID Int = null, 
	@Country NVarChar(15) = null, 
	@City NVarChar(15) = null, 
	@BirthDate DateTime = null, 
	@Address NVarChar(60) = null, 
	@paging_PageIndex int = -1, -- page number selected by the user
	@paging_RecordsperPage int = -1, -- number of items on the page
	@paging_OrderByColumn varchar(100) = '', -- name of column to order things by
	@paging_Ascending bit = 1, -- order column ascending or descending
	@paging_Count int out -- number of items in the collection
)
AS

CREATE TABLE #tmpTable 
(
	[EmployeeID] Int
)

DECLARE @total__ivqatedr int
DECLARE @orderByColumnIndex int
-- remove top x values from the temp table based upon the specific page requested
SET @total__ivqatedr = (@paging_RecordsperPage * @paging_PageIndex)
IF (@total__ivqatedr <> 0) AND (@paging_PageIndex <> -1)
BEGIN
	SET ROWCOUNT @total__ivqatedr
END
INSERT INTO #tmpTable
(
	[employeeid]
)
SELECT
	[Employees].[employeeid]
FROM
[Employees]
WHERE
((@TitleOfCourtesy is null) OR ([Employees].[TitleOfCourtesy] LIKE @TitleOfCourtesy)) AND
((@Title is null) OR ([Employees].[Title] LIKE @Title)) AND
((@ReportsTo is null) OR ([Employees].[ReportsTo] = @ReportsTo)) AND
((@Region is null) OR ([Employees].[Region] LIKE @Region)) AND
((@PostalCode is null) OR ([Employees].[PostalCode] LIKE @PostalCode)) AND
((@PhotoPath is null) OR ([Employees].[PhotoPath] LIKE @PhotoPath)) AND
((@LastName is null) OR ([Employees].[LastName] LIKE @LastName)) AND
((@HomePhone is null) OR ([Employees].[HomePhone] LIKE @HomePhone)) AND
((@HireDate is null) OR ([Employees].[HireDate] = @HireDate)) AND
((@FirstName is null) OR ([Employees].[FirstName] LIKE @FirstName)) AND
((@Extension is null) OR ([Employees].[Extension] LIKE @Extension)) AND
((@EmployeeID is null) OR ([Employees].[EmployeeID] = @EmployeeID)) AND
((@Country is null) OR ([Employees].[Country] LIKE @Country)) AND
((@City is null) OR ([Employees].[City] LIKE @City)) AND
((@BirthDate is null) OR ([Employees].[BirthDate] = @BirthDate)) AND
((@Address is null) OR ([Employees].[Address] LIKE @Address))
ORDER BY
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'TitleOfCourtesy' THEN [Employees].[TitleOfCourtesy] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'TitleOfCourtesy' THEN [Employees].[TitleOfCourtesy] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'Title' THEN [Employees].[Title] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'Title' THEN [Employees].[Title] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'ReportsTo' THEN [Employees].[ReportsTo] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'ReportsTo' THEN [Employees].[ReportsTo] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'Region' THEN [Employees].[Region] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'Region' THEN [Employees].[Region] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'PostalCode' THEN [Employees].[PostalCode] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'PostalCode' THEN [Employees].[PostalCode] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'PhotoPath' THEN [Employees].[PhotoPath] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'PhotoPath' THEN [Employees].[PhotoPath] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'LastName' THEN [Employees].[LastName] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'LastName' THEN [Employees].[LastName] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'HomePhone' THEN [Employees].[HomePhone] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'HomePhone' THEN [Employees].[HomePhone] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'HireDate' THEN [Employees].[HireDate] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'HireDate' THEN [Employees].[HireDate] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'FirstName' THEN [Employees].[FirstName] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'FirstName' THEN [Employees].[FirstName] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'Extension' THEN [Employees].[Extension] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'Extension' THEN [Employees].[Extension] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'EmployeeID' THEN [Employees].[EmployeeID] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'EmployeeID' THEN [Employees].[EmployeeID] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'Country' THEN [Employees].[Country] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'Country' THEN [Employees].[Country] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'City' THEN [Employees].[City] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'City' THEN [Employees].[City] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'BirthDate' THEN [Employees].[BirthDate] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'BirthDate' THEN [Employees].[BirthDate] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'Address' THEN [Employees].[Address] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'Address' THEN [Employees].[Address] END END

	,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'created_by' THEN [Employees].[created_by] END END DESC
	,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'created_by' THEN [Employees].[created_by] END END
	,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'created_date' THEN [Employees].[created_date] END END DESC
	,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'created_date' THEN [Employees].[created_date] END END
	,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'modified_by' THEN [Employees].[modified_by] END END DESC
	,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'modified_by' THEN [Employees].[modified_by] END END
	,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'modified_date' THEN [Employees].[modified_date] END END DESC
	,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'modified_date' THEN [Employees].[modified_date] END END
-- set @paging_Count based on the rows moved in the previous statement
SET ROWCOUNT 0
SET @paging_Count = (
SELECT count(*)
FROM
[Employees]
WHERE
((@TitleOfCourtesy is null) OR ([Employees].[TitleOfCourtesy] LIKE @TitleOfCourtesy)) AND
((@Title is null) OR ([Employees].[Title] LIKE @Title)) AND
((@ReportsTo is null) OR ([Employees].[ReportsTo] = @ReportsTo)) AND
((@Region is null) OR ([Employees].[Region] LIKE @Region)) AND
((@PostalCode is null) OR ([Employees].[PostalCode] LIKE @PostalCode)) AND
((@PhotoPath is null) OR ([Employees].[PhotoPath] LIKE @PhotoPath)) AND
((@LastName is null) OR ([Employees].[LastName] LIKE @LastName)) AND
((@HomePhone is null) OR ([Employees].[HomePhone] LIKE @HomePhone)) AND
((@HireDate is null) OR ([Employees].[HireDate] = @HireDate)) AND
((@FirstName is null) OR ([Employees].[FirstName] LIKE @FirstName)) AND
((@Extension is null) OR ([Employees].[Extension] LIKE @Extension)) AND
((@EmployeeID is null) OR ([Employees].[EmployeeID] = @EmployeeID)) AND
((@Country is null) OR ([Employees].[Country] LIKE @Country)) AND
((@City is null) OR ([Employees].[City] LIKE @City)) AND
((@BirthDate is null) OR ([Employees].[BirthDate] = @BirthDate)) AND
((@Address is null) OR ([Employees].[Address] LIKE @Address))
)

-- remove top x values from the temp table based upon the specific page requested
SET @total__ivqatedr = (@paging_RecordsperPage * @paging_PageIndex) - @paging_RecordsperPage
IF (@total__ivqatedr <> 0) AND (@paging_PageIndex <> -1)
BEGIN
	SET ROWCOUNT @total__ivqatedr
	DELETE FROM #tmpTable
END

-- return the number of rows requested as the page size
IF (@paging_PageIndex <> -1)
BEGIN
SET ROWCOUNT @paging_RecordsperPage
END
SELECT
	[Employees].[titleofcourtesy],
	[Employees].[title],
	[Employees].[reportsto],
	[Employees].[region],
	[Employees].[postalcode],
	[Employees].[photopath],
	[Employees].[photo],
	[Employees].[notes],
	[Employees].[lastname],
	[Employees].[homephone],
	[Employees].[hiredate],
	[Employees].[firstname],
	[Employees].[extension],
	[Employees].[employeeid],
	[Employees].[country],
	[Employees].[city],
	[Employees].[birthdate],
	[Employees].[address],
	[Employees].[created_by],
	[Employees].[created_date],
	[Employees].[modified_by],
	[Employees].[modified_date],
	[Employees].[time_stamp]
FROM
	[#tmpTable]
	INNER JOIN [Employees] ON #tmpTable.[employeeid] = [EMPLOYEES].[employeeid]
ORDER BY
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'TitleOfCourtesy' THEN [Employees].[TitleOfCourtesy] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'TitleOfCourtesy' THEN [Employees].[TitleOfCourtesy] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'Title' THEN [Employees].[Title] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'Title' THEN [Employees].[Title] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'ReportsTo' THEN [Employees].[ReportsTo] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'ReportsTo' THEN [Employees].[ReportsTo] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'Region' THEN [Employees].[Region] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'Region' THEN [Employees].[Region] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'PostalCode' THEN [Employees].[PostalCode] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'PostalCode' THEN [Employees].[PostalCode] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'PhotoPath' THEN [Employees].[PhotoPath] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'PhotoPath' THEN [Employees].[PhotoPath] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'LastName' THEN [Employees].[LastName] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'LastName' THEN [Employees].[LastName] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'HomePhone' THEN [Employees].[HomePhone] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'HomePhone' THEN [Employees].[HomePhone] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'HireDate' THEN [Employees].[HireDate] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'HireDate' THEN [Employees].[HireDate] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'FirstName' THEN [Employees].[FirstName] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'FirstName' THEN [Employees].[FirstName] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'Extension' THEN [Employees].[Extension] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'Extension' THEN [Employees].[Extension] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'EmployeeID' THEN [Employees].[EmployeeID] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'EmployeeID' THEN [Employees].[EmployeeID] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'Country' THEN [Employees].[Country] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'Country' THEN [Employees].[Country] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'City' THEN [Employees].[City] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'City' THEN [Employees].[City] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'BirthDate' THEN [Employees].[BirthDate] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'BirthDate' THEN [Employees].[BirthDate] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'Address' THEN [Employees].[Address] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'Address' THEN [Employees].[Address] END END

	,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'created_by' THEN [Employees].[created_by] END END DESC
	,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'created_by' THEN [Employees].[created_by] END END
	,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'created_date' THEN [Employees].[created_date] END END DESC
	,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'created_date' THEN [Employees].[created_date] END END
	,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'modified_by' THEN [Employees].[modified_by] END END DESC
	,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'modified_by' THEN [Employees].[modified_by] END END
	,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'modified_date' THEN [Employees].[modified_date] END END DESC
	,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'modified_date' THEN [Employees].[modified_date] END END
DROP TABLE #tmpTable

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_EmployeeUpdate]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_EmployeeUpdate]
GO


SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[gen_EmployeeUpdate]
(
	@TitleOfCourtesy NVarChar(25),
	@Title NVarChar(30),
	@ReportsTo Int,
	@Region NVarChar(15),
	@PostalCode NVarChar(10),
	@PhotoPath NVarChar(255),
	@Photo Image,
	@Notes NText,
	@LastName NVarChar(20),
	@HomePhone NVarChar(24),
	@HireDate DateTime,
	@FirstName NVarChar(10),
	@Extension NVarChar(4),
	@Country NVarChar(15),
	@City NVarChar(15),
	@BirthDate DateTime,
	@Address NVarChar(60),
	@modified_by varchar (50),
	@modified_date datetime = null,
	@Original_EmployeeID Int,
	@Original_time_stamp timestamp

)
AS

IF (@modified_date IS NULL)
SET @modified_date = GetDate();

SET NOCOUNT OFF;
UPDATE 
[Employees] 
SET
[Address] = @Address,
[BirthDate] = @BirthDate,
[City] = @City,
[Country] = @Country,
[Extension] = @Extension,
[FirstName] = @FirstName,
[HireDate] = @HireDate,
[HomePhone] = @HomePhone,
[LastName] = @LastName,
[Notes] = @Notes,
[Photo] = @Photo,
[PhotoPath] = @PhotoPath,
[PostalCode] = @PostalCode,
[Region] = @Region,
[ReportsTo] = @ReportsTo,
[Title] = @Title,
[TitleOfCourtesy] = @TitleOfCourtesy,
[modified_by] = @modified_by,
[modified_date] = @modified_date

WHERE
	[Employees].[EmployeeID] = @Original_EmployeeID AND
	[Employees].[time_stamp] = @Original_time_stamp


SELECT
	[Employees].[titleofcourtesy],
	[Employees].[title],
	[Employees].[reportsto],
	[Employees].[region],
	[Employees].[postalcode],
	[Employees].[photopath],
	[Employees].[photo],
	[Employees].[notes],
	[Employees].[lastname],
	[Employees].[homephone],
	[Employees].[hiredate],
	[Employees].[firstname],
	[Employees].[extension],
	[Employees].[employeeid],
	[Employees].[country],
	[Employees].[city],
	[Employees].[birthdate],
	[Employees].[address],
	[Employees].[created_by],
	[Employees].[created_date],
	[Employees].[modified_by],
	[Employees].[modified_date],
	[Employees].[time_stamp]
FROM 
[Employees]
WHERE
	[Employees].[EmployeeID] = @Original_EmployeeID
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_EmployeeSelectByCreatedDateRange]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_EmployeeSelectByCreatedDateRange]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[gen_EmployeeSelectByCreatedDateRange]
(
	@start_date datetime,
	@end_date datetime
)
AS

SET NOCOUNT ON;

SELECT
		[Employees].[titleofcourtesy],
	[Employees].[title],
	[Employees].[reportsto],
	[Employees].[region],
	[Employees].[postalcode],
	[Employees].[photopath],
	[Employees].[photo],
	[Employees].[notes],
	[Employees].[lastname],
	[Employees].[homephone],
	[Employees].[hiredate],
	[Employees].[firstname],
	[Employees].[extension],
	[Employees].[employeeid],
	[Employees].[country],
	[Employees].[city],
	[Employees].[birthdate],
	[Employees].[address],
	[Employees].[created_by],
	[Employees].[created_date],
	[Employees].[modified_by],
	[Employees].[modified_date],
	[Employees].[time_stamp]

FROM
[Employees]
WHERE
((([Employees].[created_date] IS NULL) AND (@start_date IS NULL)) OR (@start_date <= [Employees].[created_date])) AND 
((([Employees].[created_date] IS NULL) AND (@end_date IS NULL)) OR (@end_date >= [Employees].[created_date]))
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_EmployeeSelectByModifiedDateRange]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_EmployeeSelectByModifiedDateRange]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[gen_EmployeeSelectByModifiedDateRange]
(
	@start_date datetime,
	@end_date datetime
)
AS

SET NOCOUNT ON;

SELECT
		[Employees].[titleofcourtesy],
	[Employees].[title],
	[Employees].[reportsto],
	[Employees].[region],
	[Employees].[postalcode],
	[Employees].[photopath],
	[Employees].[photo],
	[Employees].[notes],
	[Employees].[lastname],
	[Employees].[homephone],
	[Employees].[hiredate],
	[Employees].[firstname],
	[Employees].[extension],
	[Employees].[employeeid],
	[Employees].[country],
	[Employees].[city],
	[Employees].[birthdate],
	[Employees].[address],
	[Employees].[created_by],
	[Employees].[created_date],
	[Employees].[modified_by],
	[Employees].[modified_date],
	[Employees].[time_stamp]

FROM
[Employees]
WHERE
((([Employees].[modified_date] IS NULL) AND (@start_date IS NULL)) OR (@start_date <= [Employees].[modified_date])) AND 
((([Employees].[modified_date] IS NULL) AND (@end_date IS NULL)) OR (@end_date >= [Employees].[modified_date]))
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_EmployeeSelectBySearchOr]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_EmployeeSelectBySearchOr]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[gen_EmployeeSelectBySearchOr]
(
	@TitleOfCourtesy NVarChar(25) = null, 
	@Title NVarChar(30) = null, 
	@ReportsTo Int = null, 
	@Region NVarChar(15) = null, 
	@PostalCode NVarChar(10) = null, 
	@PhotoPath NVarChar(255) = null, 
	@LastName NVarChar(20) = null, 
	@HomePhone NVarChar(24) = null, 
	@HireDate DateTime = null, 
	@FirstName NVarChar(10) = null, 
	@Extension NVarChar(4) = null, 
	@EmployeeID Int = null, 
	@Country NVarChar(15) = null, 
	@City NVarChar(15) = null, 
	@BirthDate DateTime = null, 
	@Address NVarChar(60) = null, 
	@paging_PageIndex int = -1, -- page number selected by the user
	@paging_RecordsperPage int = -1, -- number of items on the page
	@paging_OrderByColumn varchar(100) = '', -- name of column to order things by
	@paging_Ascending bit = 1, -- order column ascending or descending
	@paging_Count int out -- number of items in the collection
)
AS

CREATE TABLE #tmpTable 
(
	[EmployeeID] Int
)

DECLARE @total__ivqatedr int
DECLARE @orderByColumnIndex int
-- remove top x values from the temp table based upon the specific page requested
SET @total__ivqatedr = (@paging_RecordsperPage * @paging_PageIndex)
IF (@total__ivqatedr <> 0) AND (@paging_PageIndex <> -1)
BEGIN
	SET ROWCOUNT @total__ivqatedr
END
INSERT INTO #tmpTable
(
	[employeeid]
)
SELECT
	[Employees].[employeeid]
FROM
[Employees]
WHERE
((@TitleOfCourtesy is null) OR ([Employees].[TitleOfCourtesy] LIKE @TitleOfCourtesy)) OR
((@Title is null) OR ([Employees].[Title] LIKE @Title)) OR
((@ReportsTo is null) OR ([Employees].[ReportsTo] = @ReportsTo)) OR
((@Region is null) OR ([Employees].[Region] LIKE @Region)) OR
((@PostalCode is null) OR ([Employees].[PostalCode] LIKE @PostalCode)) OR
((@PhotoPath is null) OR ([Employees].[PhotoPath] LIKE @PhotoPath)) OR
((@LastName is null) OR ([Employees].[LastName] LIKE @LastName)) OR
((@HomePhone is null) OR ([Employees].[HomePhone] LIKE @HomePhone)) OR
((@HireDate is null) OR ([Employees].[HireDate] = @HireDate)) OR
((@FirstName is null) OR ([Employees].[FirstName] LIKE @FirstName)) OR
((@Extension is null) OR ([Employees].[Extension] LIKE @Extension)) OR
((@EmployeeID is null) OR ([Employees].[EmployeeID] = @EmployeeID)) OR
((@Country is null) OR ([Employees].[Country] LIKE @Country)) OR
((@City is null) OR ([Employees].[City] LIKE @City)) OR
((@BirthDate is null) OR ([Employees].[BirthDate] = @BirthDate)) OR
((@Address is null) OR ([Employees].[Address] LIKE @Address))
ORDER BY
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'TitleOfCourtesy' THEN [Employees].[TitleOfCourtesy] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'TitleOfCourtesy' THEN [Employees].[TitleOfCourtesy] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'Title' THEN [Employees].[Title] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'Title' THEN [Employees].[Title] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'ReportsTo' THEN [Employees].[ReportsTo] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'ReportsTo' THEN [Employees].[ReportsTo] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'Region' THEN [Employees].[Region] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'Region' THEN [Employees].[Region] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'PostalCode' THEN [Employees].[PostalCode] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'PostalCode' THEN [Employees].[PostalCode] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'PhotoPath' THEN [Employees].[PhotoPath] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'PhotoPath' THEN [Employees].[PhotoPath] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'LastName' THEN [Employees].[LastName] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'LastName' THEN [Employees].[LastName] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'HomePhone' THEN [Employees].[HomePhone] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'HomePhone' THEN [Employees].[HomePhone] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'HireDate' THEN [Employees].[HireDate] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'HireDate' THEN [Employees].[HireDate] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'FirstName' THEN [Employees].[FirstName] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'FirstName' THEN [Employees].[FirstName] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'Extension' THEN [Employees].[Extension] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'Extension' THEN [Employees].[Extension] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'EmployeeID' THEN [Employees].[EmployeeID] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'EmployeeID' THEN [Employees].[EmployeeID] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'Country' THEN [Employees].[Country] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'Country' THEN [Employees].[Country] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'City' THEN [Employees].[City] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'City' THEN [Employees].[City] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'BirthDate' THEN [Employees].[BirthDate] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'BirthDate' THEN [Employees].[BirthDate] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'Address' THEN [Employees].[Address] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'Address' THEN [Employees].[Address] END END

	,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'created_by' THEN [Employees].[created_by] END END DESC
	,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'created_by' THEN [Employees].[created_by] END END
	,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'created_date' THEN [Employees].[created_date] END END DESC
	,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'created_date' THEN [Employees].[created_date] END END
	,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'modified_by' THEN [Employees].[modified_by] END END DESC
	,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'modified_by' THEN [Employees].[modified_by] END END
	,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'modified_date' THEN [Employees].[modified_date] END END DESC
	,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'modified_date' THEN [Employees].[modified_date] END END
-- set @paging_Count based on the rows moved in the previous statement
SET ROWCOUNT 0
SET @paging_Count = (
SELECT count(*)
FROM
[Employees]
WHERE
((@TitleOfCourtesy is null) OR ([Employees].[TitleOfCourtesy] LIKE @TitleOfCourtesy)) OR
((@Title is null) OR ([Employees].[Title] LIKE @Title)) OR
((@ReportsTo is null) OR ([Employees].[ReportsTo] = @ReportsTo)) OR
((@Region is null) OR ([Employees].[Region] LIKE @Region)) OR
((@PostalCode is null) OR ([Employees].[PostalCode] LIKE @PostalCode)) OR
((@PhotoPath is null) OR ([Employees].[PhotoPath] LIKE @PhotoPath)) OR
((@LastName is null) OR ([Employees].[LastName] LIKE @LastName)) OR
((@HomePhone is null) OR ([Employees].[HomePhone] LIKE @HomePhone)) OR
((@HireDate is null) OR ([Employees].[HireDate] = @HireDate)) OR
((@FirstName is null) OR ([Employees].[FirstName] LIKE @FirstName)) OR
((@Extension is null) OR ([Employees].[Extension] LIKE @Extension)) OR
((@EmployeeID is null) OR ([Employees].[EmployeeID] = @EmployeeID)) OR
((@Country is null) OR ([Employees].[Country] LIKE @Country)) OR
((@City is null) OR ([Employees].[City] LIKE @City)) OR
((@BirthDate is null) OR ([Employees].[BirthDate] = @BirthDate)) OR
((@Address is null) OR ([Employees].[Address] LIKE @Address))
)

-- remove top x values from the temp table based upon the specific page requested
SET @total__ivqatedr = (@paging_RecordsperPage * @paging_PageIndex) - @paging_RecordsperPage
IF (@total__ivqatedr <> 0) AND (@paging_PageIndex <> -1)
BEGIN
	SET ROWCOUNT @total__ivqatedr
	DELETE FROM #tmpTable
END

-- return the number of rows requested as the page size
IF (@paging_PageIndex <> -1)
BEGIN
SET ROWCOUNT @paging_RecordsperPage
END
SELECT
	[Employees].[titleofcourtesy],
	[Employees].[title],
	[Employees].[reportsto],
	[Employees].[region],
	[Employees].[postalcode],
	[Employees].[photopath],
	[Employees].[photo],
	[Employees].[notes],
	[Employees].[lastname],
	[Employees].[homephone],
	[Employees].[hiredate],
	[Employees].[firstname],
	[Employees].[extension],
	[Employees].[employeeid],
	[Employees].[country],
	[Employees].[city],
	[Employees].[birthdate],
	[Employees].[address],
	[Employees].[created_by],
	[Employees].[created_date],
	[Employees].[modified_by],
	[Employees].[modified_date],
	[Employees].[time_stamp]
FROM
	[#tmpTable]
	INNER JOIN [Employees] ON #tmpTable.[employeeid] = [EMPLOYEES].[employeeid]
ORDER BY
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'TitleOfCourtesy' THEN [Employees].[TitleOfCourtesy] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'TitleOfCourtesy' THEN [Employees].[TitleOfCourtesy] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'Title' THEN [Employees].[Title] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'Title' THEN [Employees].[Title] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'ReportsTo' THEN [Employees].[ReportsTo] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'ReportsTo' THEN [Employees].[ReportsTo] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'Region' THEN [Employees].[Region] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'Region' THEN [Employees].[Region] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'PostalCode' THEN [Employees].[PostalCode] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'PostalCode' THEN [Employees].[PostalCode] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'PhotoPath' THEN [Employees].[PhotoPath] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'PhotoPath' THEN [Employees].[PhotoPath] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'LastName' THEN [Employees].[LastName] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'LastName' THEN [Employees].[LastName] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'HomePhone' THEN [Employees].[HomePhone] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'HomePhone' THEN [Employees].[HomePhone] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'HireDate' THEN [Employees].[HireDate] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'HireDate' THEN [Employees].[HireDate] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'FirstName' THEN [Employees].[FirstName] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'FirstName' THEN [Employees].[FirstName] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'Extension' THEN [Employees].[Extension] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'Extension' THEN [Employees].[Extension] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'EmployeeID' THEN [Employees].[EmployeeID] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'EmployeeID' THEN [Employees].[EmployeeID] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'Country' THEN [Employees].[Country] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'Country' THEN [Employees].[Country] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'City' THEN [Employees].[City] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'City' THEN [Employees].[City] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'BirthDate' THEN [Employees].[BirthDate] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'BirthDate' THEN [Employees].[BirthDate] END END, 
	CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'Address' THEN [Employees].[Address] END END DESC, 
	CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'Address' THEN [Employees].[Address] END END

	,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'created_by' THEN [Employees].[created_by] END END DESC
	,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'created_by' THEN [Employees].[created_by] END END
	,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'created_date' THEN [Employees].[created_date] END END DESC
	,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'created_date' THEN [Employees].[created_date] END END
	,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'modified_by' THEN [Employees].[modified_by] END END DESC
	,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'modified_by' THEN [Employees].[modified_by] END END
	,CASE @paging_Ascending WHEN 0 THEN CASE @paging_OrderByColumn WHEN 'modified_date' THEN [Employees].[modified_date] END END DESC
	,CASE @paging_Ascending WHEN 1 THEN CASE @paging_OrderByColumn WHEN 'modified_date' THEN [Employees].[modified_date] END END
DROP TABLE #tmpTable

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_EmployeeSelect]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_EmployeeSelect]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[gen_EmployeeSelect]
AS

SET NOCOUNT ON;

SELECT
		[Employees].[titleofcourtesy],
	[Employees].[title],
	[Employees].[reportsto],
	[Employees].[region],
	[Employees].[postalcode],
	[Employees].[photopath],
	[Employees].[photo],
	[Employees].[notes],
	[Employees].[lastname],
	[Employees].[homephone],
	[Employees].[hiredate],
	[Employees].[firstname],
	[Employees].[extension],
	[Employees].[employeeid],
	[Employees].[country],
	[Employees].[city],
	[Employees].[birthdate],
	[Employees].[address],
	[Employees].[created_by],
	[Employees].[created_date],
	[Employees].[modified_by],
	[Employees].[modified_date],
	[Employees].[time_stamp]

FROM 
[Employees]
GO

SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[gen_EmployeeSelectByEmployeePks]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
	drop procedure [dbo].[gen_EmployeeSelectByEmployeePks]
GO

SET QUOTED_IDENTIFIER ON 
GO
SET ANSI_NULLS ON 
GO


CREATE PROCEDURE [dbo].[gen_EmployeeSelectByEmployeePks]
(
	@xml ntext,
	@direction char(4),
	@levels int
)
AS

DECLARE @hDoc int
DECLARE @newItemCount int
DECLARE @count int
DECLARE @index int
EXEC sp_xml_preparedocument @hDoc OUTPUT, @xml

CREATE TABLE #TmpIds ([EmployeeID] Int)
INSERT INTO #TmpIds([EmployeeID])
SELECT
	[Employees].[ReportsTo]
FROM 
[Employees]
WHERE
	[Employees].[ReportsTo] IN (SELECT [EmployeeID]
				FROM OpenXML(@hDoc, '//Employees', 2)
				WITH ([EmployeeID] Int 'EmployeeID'))

IF (@direction = 'DOWN' OR @direction = 'BOTH')
BEGIN
	SET @newItemCount = (SELECT COUNT(*) FROM #TmpIds)
	SET @count = @newItemCount
	SET @index = 1
	WHILE(@newItemCount > 0 and @index <= @levels)
	BEGIN
		INSERT INTO #TmpIds([EmployeeID])
		SELECT
	[EmployeeID]
		FROM 
			[Employees]
			INNER JOIN #TmpIds ON 
[Employees].[ReportsTo] = #TmpIds.[EmployeeID]
		WHERE
			([Employees].[ReportsTo] NOT IN (SELECT [EmployeeID] from #TmpIds))

		SET @newItemCount = (SELECT COUNT(*) FROM #TmpIds) - @count
		SET @count = (SELECT COUNT(*) FROM #TmpIds)
		SET @index = @index + 1
	END
END
IF (@direction = 'UP' OR @direction = 'BOTH')
BEGIN
	SET @newItemCount = (SELECT COUNT(*) FROM #TmpIds)
	SET @count = @newItemCount
	SET @index = 1
	WHILE(@newItemCount > 0 and @index <= @levels)
	BEGIN
		INSERT INTO #TmpIds([EmployeeID])
		SELECT
	[Employees].[EmployeeID]
		FROM #TmpIds INNER JOIN [Employees] ON 
			[Employees].[ReportsTo] = #TmpIds.[EmployeeID]
		WHERE
			([Employees].[ReportsTo] NOT IN (SELECT [EmployeeID] from #TmpIds))
		SET @newItemCount = (SELECT COUNT(*) FROM #TmpIds) - @count
		SET @count = (SELECT COUNT(*) FROM #TmpIds)
		SET @index = @index + 1
	END
END

SELECT
		[Employees].[titleofcourtesy],
	[Employees].[title],
	[Employees].[reportsto],
	[Employees].[region],
	[Employees].[postalcode],
	[Employees].[photopath],
	[Employees].[photo],
	[Employees].[notes],
	[Employees].[lastname],
	[Employees].[homephone],
	[Employees].[hiredate],
	[Employees].[firstname],
	[Employees].[extension],
	[Employees].[employeeid],
	[Employees].[country],
	[Employees].[city],
	[Employees].[birthdate],
	[Employees].[address],
	[Employees].[created_by],
	[Employees].[created_date],
	[Employees].[modified_by],
	[Employees].[modified_date],
	[Employees].[time_stamp]

FROM 
[Employees]
WHERE
			([Employees].[ReportsTo] IN (SELECT [EmployeeID] from #TmpIds))

DROP TABLE #TmpIds
exec sp_xml_removeDocument @hDoc

GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

